In [1]:
import pandas as pandas
#import xlrd
#xls = xlrd.open_workbook(r'Stock_Data.xlsx', on_demand=True)
#print xls.sheet_names()

The sheet names are [u'Original', u'deduped', u'Sheet7', u'AmericanExchanges', u'ForeignAndPink', u'Sheet2']

In [2]:
data = pandas.read_excel('Stock_Data.xlsx', 'AmericanExchanges')
import copy
data_backup=copy.deepcopy(data)

First we create a date column instead of the integer formatting

In [3]:
data['Dates']=pandas.to_datetime(data['Names Date'].values, format = '%Y%m%d')
In [4]:
data = data[pandas.notnull(data['Company Name'])]
In [5]:
pcosset = set(data['PERMCO'].values)
pcoslist = list(set(data['PERMCO'].values))
# Make sure no Permco's are repeated
assert len(pcosset) ==len(pcoslist)
In [6]:
import csv
with open('w_pco.csv', 'wb') as csvfile:
    w = csv.writer(csvfile, delimiter=' ')
    for p in pcosset:
        cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
        row = str(p) + ', '+ ', '.join(cpnies) 
        w.writerow(row)
In [10]:
for p in pcosset:
    cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
    row = str(p) + ', '+ ', '.join(cpnies) 
    fig = plt.figure()
    fig.set_size_inches(16,4)
    to_plot =data[data['PERMCO'] == p]
    to_plot.plot('Dates', 'Price or Bid/Ask Average', subplots=True, title =row)
    plt.show()
    plt.close()
In []: